package com.github.vortexdev.yachtclub.repository.mysql;

import com.github.vortexdev.yachtclub.models.Owner;
import com.github.vortexdev.yachtclub.models.Regatta;
import com.github.vortexdev.yachtclub.models.Yacht;
import com.github.vortexdev.yachtclub.repository.YachtRepository;
import com.github.vortexdev.yachtclub.util.db.Page;
import com.github.vortexdev.yachtclub.util.db.QueryBuilder;
import com.github.vortexdev.yachtclub.util.db.Sort;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.jdbc.support.lob.DefaultLobHandler;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.LobHandler;

import javax.sql.DataSource;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;
import java.util.Map;

/**
 * @author Sergey Parakhin (sergey.parahin@gmail.com)
 */
public class MysqlYachtRepository implements YachtRepository {
    private JdbcTemplate jdbcTemplate;

    private static final String SELECT_ALL_WITH_OWNERS_PAGINATION_SQL_TEMPLATE
            = "SELECT yachts.*, owners.name AS owner_name "
            + "FROM yachts LEFT JOIN owners ON owners.ownerId = yachts.ownerId LIMIT ?, ?";
    private static final String SELECT_ALL_WITH_OWNERS_SQL_TEMPLATE
            = "SELECT yachts.*, owners.name AS owner_name "
            + "FROM yachts LEFT JOIN owners ON owners.ownerId = yachts.ownerId ";
    private static final String COUNT_YACHTS_SQL = "SELECT count(*) FROM yachts;";
    private static final String INSERT_YACHT_SQL_TEMPLATE = "INSERT INTO yachts VALUES"
            + "(0, ?, ?, ?, ?, ?, ?, ?, ?);";
    private static final String INSERT_YACHT_REGATTA_LINK
            = "INSERT INTO regatta_participants VALUES(?, ?);";
    private static final String SELECT_YACHT_SQL_TEMPLATE
            = "SELECT * FROM yachts WHERE yachtId = ?";
    private static final String SELECT_YACHT_WITH_OWNER_SQL_TEMPLATE
            = "SELECT yachts.*, owners.name AS owner_name "
            + "FROM yachts LEFT JOIN owners ON owners.ownerId = yachts.ownerId WHERE yachts.yachtId = ?;";

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    @Override
    public Yacht find(int id) {
        List<Yacht> yachts = jdbcTemplate.query(
                SELECT_YACHT_SQL_TEMPLATE,
                new Object[]{id}, YACHT_MAPPER
        );
        return yachts.isEmpty() ? null : yachts.get(0);
    }

    @Override
    public Yacht findWithOwnerName(int id) {
        List<Yacht> yachts = jdbcTemplate.query(
                SELECT_YACHT_WITH_OWNER_SQL_TEMPLATE,
                new Object[]{id}, YACHT_EXTENDED_MAPPER
        );
        return yachts.isEmpty() ? null : yachts.get(0);
    }

    @Override
    public void save(Yacht yacht) {
        KeyHolder keyHolder = new GeneratedKeyHolder();

        jdbcTemplate.update(
                new YachtAbstractLobPreparedStatementCreator(
                        new DefaultLobHandler(),
                        INSERT_YACHT_SQL_TEMPLATE,
                        "yachtId", yacht)
                , keyHolder);

        final int generatedId = keyHolder.getKey().intValue();
        yacht.setYachtId(generatedId);

        // create links with regattas (insert data into regatta_participants table)
        for (Regatta regatta : yacht.getRegattas())
            jdbcTemplate.update(
                    INSERT_YACHT_REGATTA_LINK,
                    new Object[]{regatta.getRegattaId(), generatedId}
            );
    }

    @Override
    public List<Yacht> findYachtsWithOwnerNames(Page page) {
        QueryBuilder queryBuilder = new QueryBuilder(
                SELECT_ALL_WITH_OWNERS_PAGINATION_SQL_TEMPLATE
        ).page(page);

        return jdbcTemplate.query(
                queryBuilder.getQuery(),
                YACHT_EXTENDED_MAPPER,
                queryBuilder.getParams()
        );
    }

    @Override
    public List<Yacht> findYachtsWithOwnerNames(Page page, Sort sort) {
        QueryBuilder queryBuilder = new QueryBuilder(
                SELECT_ALL_WITH_OWNERS_SQL_TEMPLATE
        ).orderBy(sort).page(page);

        return jdbcTemplate.query(
                queryBuilder.getQuery(),
                YACHT_EXTENDED_MAPPER,
                queryBuilder.getParams()
        );
    }

    @Override
    public List<Yacht> findYachtsWithOwnerNames(Page page,
                                                Map<String, Object> criteria) {
        QueryBuilder queryBuilder = new QueryBuilder(
                SELECT_ALL_WITH_OWNERS_SQL_TEMPLATE
        ).filter(criteria).page(page);

        return jdbcTemplate.query(
                queryBuilder.getQuery(),
                YACHT_EXTENDED_MAPPER,
                queryBuilder.getParams()
        );
    }

    @Override
    public List<Yacht> findYachtsWithOwnerNames(Page page,
                                                Map<String, Object> criteria,
                                                Sort sort) {
        QueryBuilder queryBuilder = new QueryBuilder(
                SELECT_ALL_WITH_OWNERS_SQL_TEMPLATE
        ).filter(criteria).orderBy(sort).page(page);

        return jdbcTemplate.query(
                queryBuilder.getQuery(),
                YACHT_EXTENDED_MAPPER,
                queryBuilder.getParams()
        );
    }

    @Override
    public int countYachts() {
        return jdbcTemplate.queryForInt(COUNT_YACHTS_SQL);
    }

    private static final RowMapper<Yacht> YACHT_MAPPER = new RowMapper<Yacht>() {
        public Yacht mapRow(ResultSet rs, int rowNum) throws SQLException {
            Yacht yacht = new Yacht();
            yacht.setYachtId(rs.getInt("yachtId"));
            yacht.setName(rs.getString("name"));
            yacht.setDrive(rs.getString("drive"));
            yacht.setHull(rs.getString("hull"));
            yacht.setPurpose(rs.getString("purpose"));
            yacht.setDescription(rs.getString("description"));
            yacht.setOwner(new Owner(rs.getInt("ownerId")));
            yacht.setPhoto(new DefaultLobHandler().getBlobAsBytes(rs, "photo"));
            yacht.setContentType(rs.getString("content_type"));

            return yacht;
        }
    };

    private static final RowMapper<Yacht> YACHT_EXTENDED_MAPPER = new RowMapper<Yacht>() {
        public Yacht mapRow(ResultSet rs, int rowNum) throws SQLException {
            Yacht yacht = new Yacht();
            yacht.setYachtId(rs.getInt("yachtId"));
            yacht.setName(rs.getString("name"));
            yacht.setDrive(rs.getString("drive"));
            yacht.setHull(rs.getString("hull"));
            yacht.setPurpose(rs.getString("purpose"));
            yacht.setDescription(rs.getString("description"));
            Owner owner = new Owner(rs.getInt("ownerId"));
            owner.setName(rs.getString("owner_name"));
            yacht.setOwner(owner);
            return yacht;
        }
    };

    private static class YachtAbstractLobPreparedStatementCreator extends AbstractLobPreparedStatementCreator {
        private Yacht yacht;

        public YachtAbstractLobPreparedStatementCreator(LobHandler lobHandler, String sql, String keyColumn, Yacht yacht) {
            super(lobHandler, sql, keyColumn);
            this.yacht = yacht;
        }

        @Override
        protected void setValues(PreparedStatement ps, LobCreator
                lobCreator) throws SQLException, DataAccessException {
            if(yacht.getOwner() != null)
                ps.setInt(1, yacht.getOwner().getOwnerId());
            else
                ps.setNull(1, Types.INTEGER);
            ps.setString(2, yacht.getName());
            ps.setString(3, yacht.getHull());
            ps.setString(4, yacht.getDrive());
            ps.setString(5, yacht.getPurpose());
            ps.setString(6, yacht.getDescription());
            lobCreator.setBlobAsBytes(ps, 7, yacht.getPhoto());
            ps.setString(8, yacht.getContentType());
        }
    }
}
